#!/usr/bin/env python
# -*- coding: utf-8 -*-
# 时间     : 2019-12-11 15:54:52
# 作者     : DL (584054432@qq.com)
# 网址     : https://gitee.com/dingliangPython/
# 软件版本 : Python3.7.3
# 功能     ：
import logging
import os
import sqlite3
import sys

import cx_Oracle
import pymssql
import pymysql
import pyodbc

import tools

if sys.version_info.major==3 and sys.version_info.minor<=4:
    from DBUtils.PooledDB import PooledDB
else:
    from dbutils.pooled_db import PooledDB

os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.ZHS16GBK'


class Sql(object):
    """docstring for Sql"""

    # 初始化
    # __pool=None
    def __init__(self, _type = "mysql", *args, **kwargs):
        """摘要
        功能描述：
        参数:
            _type: [默认参数描述: 必要参数 ] (默认: {"mysql"})
            *args: [描述: 保留]
            **kwargs: [
                        * username=用户名
                        * password=密码,
                        * host=主机IP/主机名
                        * port=端口，默认为数据库默认端口
                        * database=数据库实例
                        * dsn=odbc dsn名称
                        * databasepath= 数据库路径，sqlite,access
                        * pool = True 是否启用连接池，只支持，MYSQL,MSSQL,ORACLE
                        * 以下参数只针对连接池
                        * maxconnections= 20 最大连接数,
                        * mincached=1,# 初始化时，连接池至少创建的空闲的连接，0表示不创建
                        * maxcached=2,# 连接池空闲的最多连接数，0和None表示没有限制
                        * maxshared=0,# 连接池中最多共享的连接数量，0和None表示全部共享
                        * blocking=True  # 链接池中如果没有可用共享连接后，是否阻塞等待，True表示等待，False表示不等待然后报错
                    ]
        """
        super(Sql, self).__init__()
        self.__dict__ = kwargs
        self.args = args
        self.__type = _type.lower()
        self.log = logging.getLogger('main.SqlClient')
        self.__pool = None
        self.__connect = self.__get_database_connect()

    # 默认没有的属性为None
    def __getattr__(self, key):
        return None

    def __call__(self):
        return self.__get_database_connect()

    # 检测连接
    def __get_connect(self, connect):
        if connect:
            return connect
        else:
            rec = tools.Set_Message(" {}：数据库连接失败，是否退出程序".format(self.__type.upper()))
            if rec == 1: os._exit(0)

    # 获取access连接
    def __get_access_connect(self):
        ext = os.path.splitext(self.databasepath)[1][1:].lower() if self.databasepath else None
        driver_list = pyodbc.drivers()
        # odbc驱动
        odbc_driver = [x for x in driver_list[::-1] if ext in x]
        driver_str = ""
        if self.dsn:
            driver_str = "DSN={};UID=admin;PWD={};".format(self.dsn, self.password or '')
        elif ext == 'accdb' and not odbc_driver:
            rec = tools.Set_Message("没有accdb驱动，请安装ACCESS2010或以上驱动程序,确认退出，取消继续")
            if rec == 1: os._exit(0)
        else:
            driver_str = "DRIVER={{{}}};DBQ={};Pwd={};ExtendedAnsiSQL=1;".format(odbc_driver[0], self.databasepath,
                                                                                 self.password or "")
        connect = pyodbc.connect(driver_str, timeout = 1, autocommit = True)
        return self.__get_connect(connect)

    # 选择驱动
    def __get_drive_select(self):
        drive = None
        self.host = self.host or '127.0.0.1'
        if self.__type in ('mssql', 'sqlserver', 'sql server'):
            drive = pymssql
            self.port = self.port or 1433
        elif self.__type == 'mysql':
            drive = pymysql
            self.port = self.port or 3306
        elif self.__type == 'oracle':
            drive = cx_Oracle
            self.port = self.port or 1521
        return drive

    # 获取连接、连接池
    def __get_database_connect(self):
        # 连接池模式
        # try:
            if self.pool and self.__type in ('mssql', 'sqlserver', 'sql server', 'mysql', 'oracle'):
                # 存在池，就取出连接
                if self.__pool:
                    return self.__pool.connection()
                # 驱动选择
                drive = self.__get_drive_select()
                # mysql mssql
                if self.__type != 'oracle':
                    self.__pool = PooledDB(
                            creator = drive,  # 使用链接数据库的模块
                            host = self.host,
                            user = self.username,
                            port = int(self.port),
                            password = self.password,
                            database = self.database or None,
                            maxconnections = int(self.maxconnections or 20),  # 连接池允许的最大连接数，0和None表示没有限制
                            mincached = int(self.mincached or 4),  # 初始化时，连接池至少创建的空闲的连接，0表示不创建
                            maxcached = int(self.maxcached or 0),  # 连接池空闲的最多连接数，0和None表示没有限制
                            maxshared = int(self.maxshared or 0),  # 连接池中最多共享的连接数量，0和None表示全部共享
                            blocking = True,  # 链接池中如果没有可用共享连接后，是否阻塞等待，True表示等待，False表示不等待然后报错
                            maxusage = int(self.maxusage or 10),
                            # charset='utf8'
                            )
                    self.log.info('数据库：{}连接池初始成功'.format(self.__type))
                # oracl
                else:
                    self.__pool = PooledDB(
                            creator = drive,  # 使用链接数据库的模块
                            dsn = "{}:{}/{}".format(self.host, self.port, self.database),
                            user = self.username,
                            password = self.password,
                            maxconnections = int(self.maxconnections or 20),  # 连接池允许的最大连接数，0和None表示没有限制
                            mincached = 0,  # 初始化时，连接池至少创建的空闲的连接，0表示不创建
                            maxcached = int(self.maxcached or 0),  # 连接池空闲的最多连接数，0和None表示没有限制
                            maxshared = int(self.maxshared or 0),  # 连接池中最多共享的连接数量，0和None表示全部共享
                            blocking = True,  # 链接池中如果没有可用共享连接后，是否阻塞等待，True表示等待，False表示不等待然后报错
                            maxusage = int(self.maxusage or 2),

                            )
                    self.log.info('数据库：{}连接池初始成功'.format(self.__type))
                return self.__pool.connection()
            # odbc 模式
            elif self.dsn:
                _str = "DSN={};UID={};PWD={};".format(self.dsn, self.username, self.password or '')
                connect = pyodbc.connect(_str, timeout = 1, autocommit = True)
                print("连接的是odbc")
                return self.__get_connect(connect)
            else:
                drive = self.__get_drive_select()
                if self.__type == 'access':
                    return self.__get_access_connect()
                if self.__type == 'mysql':
                    connect = drive.connect(self.host, self.username, self.password, self.database,
                                            port = int(self.port), charset = 'utf8', connect_timeout = 1)
                    # self.log.debug("连接的是pymssql")
                    return self.__get_connect(connect)
                if self.__type in ('mssql', 'sqlserver', 'sql server'):
                    connect = drive.connect(self.host, self.username, self.password, self.database,
                                            port = int(self.port), charset = 'utf8', login_timeout = 1)
                    # self.log.debug("连接的是pymssql")
                    return self.__get_connect(connect)
                if self.__type == 'oracle':
                    dsn = "{}:{}/{}".format(self.host, self.port or 1521, self.database)
                    connect = drive.connect(self.username, self.password, dsn)
                    # self.log.debug("连接的是oracle")
                    return self.__get_connect(connect)
                if self.__type == 'sqlite':
                    connect = sqlite3.connect(self.databasepath, check_same_thread = False)
                    # self.log.debug("连接的是sqlite")
                    return self.__get_connect(connect)
                else:
                    rec = tools.Set_Message("目前只支持ACCESS,MYSQL,MSSQL,ORALCE,SQLITE,确认退出")
                    if rec == 1: os._exit(0)
        # except Exception as e:
        #     self.log.warning("数据库:{}，连接失败,请检查连接参数".format(self.__type))
        #     self.log.error("异常信息：{}".format(e))

    # 查询数据
    def loadSql(self, sql, isDict = False):
        try:
            if self.__pool:
                connect = self.__pool.connection()
            else:
                connect = self.__get_database_connect()
            cursor = connect.cursor()
            cursor.execute(sql)
            value = cursor.fetchall()
            data = []
            if value and len(value) >= 1:
                # 返回元组
                if not isDict:
                    cursor.close()
                    connect.close()
                    return value
                # 返回字典
                field = tuple([d[0] for d in cursor.description])
                for v in value:
                    data.append(dict(zip(field, v)))
                cursor.close()
                connect.close()
                # del connect
                return data
            else:
                return None
        except Exception as e:
            if self.__pool:
                connect = self.__pool.connection()
            else:
                connect = self.__get_database_connect()
            connect.rollback()
            self.log.error("异常执行语句：{}".format(sql))
            self.log.error("异常信息：{}".format(e))

    # 执行 插入，更新，删除等操作语句
    # @tools.try_error
    def execSql(self, sql, valueList = []):
        try:
            if self.__pool:
                connect = self.__pool.connection()
            else:
                connect = self.__get_database_connect()

            cursor = connect.cursor()
            if not valueList:
                cursor.execute(sql)
                connect.commit()
                result = cursor.rowcount
                cursor.close()
                connect.close()
                return result
            else:
                cursor.executemany(sql, valueList)
                result = cursor.rowcount
                connect.commit()
                cursor.close()
                connect.close()
                return result
        except Exception as e:
            if self.__pool:
                connect = self.__pool.connection()
            else:
                connect = self.__get_database_connect()
            connect.rollback()

            if "ORA-00001" in str(e):
                self.log.error("主键冲突，已存在数据不需要重复上传")
            else:
                self.log.error("异常执行语句：{}{}".format(sql, ",批量值：{}".format(valueList) if valueList else ""))
                self.log.error("异常信息：{}".format(e))


# @tools.try_error
def Get_Connect_List(config):
    obj = tools.obj()
    for _, v in config.items():
        name = v.get('id') or v.get('name')
        _type = v.get('type')
        if name and _type:
            obj.__dict__.update({name: Sql(_type, **v)})
    return obj


if __name__ == '__main__':
    import loadConfig, time_ext_

    config = loadConfig.loadIni(r'D:\CODE\PYTHON\Tem\config\config.ini').read()
    print(config)
    i = Get_Connect_List(config)
    while 1:
        # print(i,dir(i))
        # break
        x = i.CICS.loadSql("select sysdate from dual", True)
        print(time_ext_.time(), x, )
        # print(id(i.device._Sql__pool))
        time_ext_.sleep(1)